package com.baturu.vin.benz.cleanData;

import com.baturu.vin.benz.BaseTest;
import com.baturu.vin.benz.cleanData.dao.BenzDealPartsDAO;
import com.google.common.collect.Sets;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;

import java.sql.*;
import java.util.Date;
import java.util.Set;

/**
 * @Author: jiangxinlei
 * @Time: 2018/2/5 14:45
 **/
@Slf4j
public class BenzDealDcRtype extends BaseTest {

    @Autowired
    private BenzDealPartsDAO dealDAO;

    @Test
    public void cleanAllData() throws Exception {

        dealDcRtype();

    }

    private void dealDcRtype() throws Exception {
        Set<String> dataBaseNames = Sets.newHashSet("DAG_DCCV","DAG_DCPC","DCEX_DCEX","MBOE_DCCAR","MBOE_DCGWGN","MBJA_DCJA","MBLA_DCLA","MBNA_DCNA","MBSM_DCSM");

        dataBaseNames.parallelStream().forEach(dataBaseName->{
            System.out.println("开始处理"+dataBaseName+"库的数据,"+(new Date()));
            String tableNameForRtype1 = "dc_rtype1_v_"+dataBaseName.toLowerCase()+"_newdata";
            String tableNameForRtype3 = "dc_rtype3_v_"+dataBaseName.toLowerCase()+"_newdata";
            try {
                dc_rtype1(dataBaseName, tableNameForRtype1);
                dc_rtype3(dataBaseName, tableNameForRtype3);
            } catch (Exception e) {
                log.error(e.getMessage(), e);
            }
        });

    }

    private void dc_rtype1(String dataBaseName, String tableName) throws ClassNotFoundException, SQLException {
        Class.forName("transbase.jdbc.Driver");
        String dburl1 = "jdbc:transbase://192.168.16.192:2034/" + dataBaseName;
        String uname1 = "tbadmin";
        String pw1 = "LMtdKNkhDHakNR3";
        Connection conn = DriverManager.getConnection(dburl1, uname1, pw1);

        String spoolSql = "SELECT * FROM dc_rtype1_v";
        Statement stmt1 = conn.createStatement();
        ResultSet rs1 = stmt1.executeQuery(spoolSql);
        if (rs1 == null) {
            rs1.close();
            stmt1.close();
            conn.close();
            return;
        }
        String insertName = "(WHC, CHASSBM, CHASS_IDENT, VEHICLE_CATEGORY, ENGINE, TRANSMISSION, STEERING, BODY, FRONT_AXLE1, FRONT_AXLE2, REAR_AXLE1, REAR_AXLE2, TRANSFER_CASE, PLATFORM, BODY_CODE, MODEL_IDX, ORDER_NUM1, PRODUCTION_NUM1, AUXILIARY_DRIVE, HEATER, RIMS, LIGHTS, WIPER, WINCH, ODOMETER, VINWHC, VIN, TRAILER, INTERIOR, PAINT1, PAINT2, PAINT3, PAINT4, PAINT5, RETARDER, DELIVERY_DATE, PRODUCTION_ORDER_REF, SERVICE_NUM, RELEASE_DATE, LEADINGAGGREGATE, ELECTRICALENGINE1, ELECTRICALENGINE2, ELECTRICALENGINE3, ELECTRICALENGINE4, ELECTRICALENGINE5, ELECTRICALENGINE6, HVBATTERY1, HVBATTERY2, HVBATTERY3, FUELCELL1, FUELCELL2, FUELCELL3, EXHAUSTSYS, FRONTAXLE3, REARAXLE3, REARAXLE4, EUAPPROVALNUM, EUAPPROVALDATE, CLUTCHVENDOR)";
        StringBuilder sb = new StringBuilder();
        int count = 0;
        while (rs1.next()) {
            count ++;

            sb.append("(").append(deal(rs1.getString(1))).append(",").append(deal(rs1.getString(2))).append(",").append(deal(rs1.getString(3))).append(",").append(deal(rs1.getString(4))).append(",").append(deal(rs1.getString(5))).append(",").append(deal(rs1.getString(6))).append(",").append(deal(rs1.getString(7))).append(",").append(deal(rs1.getString(8))).append(",").append(deal(rs1.getString(9))).append(",").append(deal(rs1.getString(10))).append(",").append(deal(rs1.getString(11))).append(",").append(deal(rs1.getString(12))).append(",").append(deal(rs1.getString(13))).append(",").append(deal(rs1.getString(14))).append(",").append(deal(rs1.getString(15))).append(",").append(deal(rs1.getString(16))).append(",").append(deal(rs1.getString(17))).append(",").append(deal(rs1.getString(18))).append(",").append(deal(rs1.getString(19))).append(",").append(deal(rs1.getString(20))).append(",").append(deal(rs1.getString(21))).append(",").append(deal(rs1.getString(22))).append(",").append(deal(rs1.getString(23))).append(",").append(deal(rs1.getString(24))).append(",").append(deal(rs1.getString(25))).append(",").append(deal(rs1.getString(26))).append(",").append(deal(rs1.getString(27))).append(",").append(deal(rs1.getString(28))).append(",").append(deal(rs1.getString(29))).append(",").append(deal(rs1.getString(30))).append(",").append(deal(rs1.getString(31))).append(",").append(deal(rs1.getString(32))).append(",").append(deal(rs1.getString(33))).append(",").append(deal(rs1.getString(34))).append(",").append(deal(rs1.getString(35))).append(",").append(deal(rs1.getString(36))).append(",").append(deal(rs1.getString(37))).append(",").append(deal(rs1.getString(38))).append(",").append(deal(rs1.getString(39))).append(",").append(deal(rs1.getString(40))).append(",").append(deal(rs1.getString(41))).append(",").append(deal(rs1.getString(42))).append(",").append(deal(rs1.getString(43))).append(",").append(deal(rs1.getString(44))).append(",").append(deal(rs1.getString(45))).append(",").append(deal(rs1.getString(46))).append(",").append(deal(rs1.getString(47))).append(",").append(deal(rs1.getString(48))).append(",").append(deal(rs1.getString(49))).append(",").append(deal(rs1.getString(50))).append(",").append(deal(rs1.getString(51))).append(",").append(deal(rs1.getString(52))).append(",").append(deal(rs1.getString(53))).append(",").append(deal(rs1.getString(54))).append(",").append(deal(rs1.getString(55))).append(",").append(deal(rs1.getString(56))).append(",").append(deal(rs1.getString(57))).append(",").append(deal(rs1.getString(58))).append(",").append(deal(rs1.getString(59))).append("),");
            if (count % 1000 == 0 && sb.length()>1) {
                System.out.println(count+"   "+dataBaseName+" dc_rtype1_v  "+(new Date()));
                sb.deleteCharAt(sb.length()-1);
                dealDAO.insert(tableName, insertName, sb.toString());
                sb = new StringBuilder();
            }
        }
        if (StringUtils.isNotBlank(sb) && sb.length()>1) {
            System.out.println(count+"   "+dataBaseName+" dc_rtype1_v  "+(new Date()));
            sb.deleteCharAt(sb.length()-1);
            dealDAO.insert(tableName, insertName, sb.toString());
        }
        rs1.close();
        stmt1.close();
        conn.close();
    }

    private void dc_rtype3(String dataBaseName, String tableName) throws ClassNotFoundException, SQLException {
        Class.forName("transbase.jdbc.Driver");
        String dburl1 = "jdbc:transbase://192.168.16.192:2034/" + dataBaseName;
        String uname1 = "tbadmin";
        String pw1 = "LMtdKNkhDHakNR3";
        Connection conn = DriverManager.getConnection(dburl1, uname1, pw1);

        String spoolSql = "SELECT * FROM dc_rtype3_v";
        Statement stmt1 = conn.createStatement();
        ResultSet rs1 = stmt1.executeQuery(spoolSql);
        if (rs1 == null) {
            rs1.close();
            stmt1.close();
            conn.close();
            return;
        }
        String insertName = "(WHC, CHASSBM, CHASS_IDENT,SEQNUM,SACODE_DATA)";
        StringBuilder sb = new StringBuilder();
        int count = 0;
        while (rs1.next()) {
            count ++;

            String WHC = rs1.getString(1);
            String CHASSBM = rs1.getString(2);
            String CHASS_IDENT = rs1.getString(3);
            String SEQNUM = rs1.getString(4);
            String SACODE_DATA = rs1.getString(5);
            sb.append("(").append(deal(WHC)).append(",").append(deal(CHASSBM)).append(",").append(deal(CHASS_IDENT))
                    .append(",").append(deal(SEQNUM)).append(",").append(deal(SACODE_DATA)).append("),");
            if (count % 1000 == 0 && sb.length()>1) {
                System.out.println(count+"  "+dataBaseName+" dc_rtype3_v  "+(new Date()));
                sb.deleteCharAt(sb.length()-1);
                dealDAO.insert(tableName, insertName, sb.toString());
                sb = new StringBuilder();
            }
        }
        if (StringUtils.isNotBlank(sb) && sb.length()>1) {
            System.out.println(count+"  "+dataBaseName+" dc_rtype3_v  "+(new Date()));
            sb.deleteCharAt(sb.length()-1);
            dealDAO.insert(tableName, insertName, sb.toString());
        }
        rs1.close();
        stmt1.close();
        conn.close();
    }


    private String deal(String str) {
        if (StringUtils.isBlank(str)) {
            return "null";
        }
        String replace = StringUtils.replace(str, "\'", "\\\'");
        return "'"+ replace +"'";
    }

}
